SYBASE™ SQL ANYWHERE™ is an ANSI SQL-compliant relational database management system (RDBMS) designed to run on a variety of platforms from server-class installations to mobile devices using the Windows Mobile operating system. SQL ANYWHERE™ is a self-managing RDBMS with high reliability, high performance, synchronization capabilities, small footprint, and a full range of SQL features across a variety of 32- and 64-bit platforms.
Currently available products, such as SQL ANYWHERE™ support manual materialized views. A manual materialized view, which can only be refreshed by complete re-computation, may be defined by any complex query. However, some query optimizers, such as the SQL ANYWHERE™ Optimizer, can use only a certain class of materialized views in the view matching process during query optimization. Although SQL ANYWHERE™ supports incremental maintenance of certain materialized views for GROUP-SELECT-PROJECT-JOIN views, what is needed is a more general class of materialized views that can be incrementally maintained. Accordingly, what is needed are methods and systems for incremental maintenance for immediate Materialized Views (iMVs). What is further needed is an RDBMS that supports an extended class of immediate materialized views, namely outerjoin views with and without aggregation. In an embodiment of the invention, the algorithms described herein may be implemented in a database or data warehouse system such as, but not limited to, SQL ANYWHERE™.
Outerjoin queries are used more and more frequently in new systems and external tools where database administrators (DBAs) or experienced database developers are not at hand to fine-tune the generated SQL statements. An example of such generated query is depicted in FIG. 1. For example, the SQL ANYWHERE™ Optimizer has sophisticated techniques for processing outerjoin queries from semantics transformations to view matching using outer-join views. It is then a necessity to extend the support to incremental maintenance of materialized views with outerjoins, as this can speed up many of the applications using the SQL ANYWHERE™ RDBMS. The goals for an efficient support of immediate materialized views with outerjoins are multifold.
Some traditional techniques for incremental maintenance of materialized views with outerjoins are based on the join-disjunctive normal form representation. A join-disjunctive normal form encodes an outerjoin query as a sequence of minimum unions of different joins. The example in FIG. 3 shows the parent-child relationship graph corresponding to the join-disjunctive normal form of the query V1 defined in Table 1. Such incremental maintenance algorithm consists of a series of steps: one step for computing and applying the primary delta (not null-supplied tuples), then a set of subsequent steps for applying secondary deltas (null-supplied tuples) to delete or insert null-supplied tuples. The primary delta is saved and reused in the computation of the secondary deltas. This computation may need to access again the base relations in order to correctly compute the null-supplied tuples. As these traditional techniques require a separate SQL statement to implement each of the needed steps, they incur inefficiencies and require resources for parsing and executing multiple SQL statements. For example, for the relation X2 of the view V1 (defined in Table 1) depicted in FIG. 3, the view update algorithm will consist of five steps: computing and applying the primary delta, and computing and applying four secondary deltas corresponding to the join-disjunctive normal form terms R1R2T1T2, R1R2, X1Y1Y2, and X1.
Accordingly, what is needed are methods and systems for incremental maintenance of materialized views with outerjoins through use of a single maintenance update statement for each materialized outer-join view.